package org.magicsoft.office.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.magicsoft.office.exception.DBException;
import org.magicsoft.office.exception.UnsupportedException;

public class DatabaseUtils {

	private static final Pattern JOIN_UPDATE_PATTERN = getUpdateSQLPattern();

	private static final Pattern ALIAS_PATTERN = getAliasPattern();

	private DatabaseUtils() {
	}

	/**
	 * 分组结果大致如下：
	 * 
	 * <ul>
	 * <li>0: 整理表达式</li>
	 * <li>1: update表</li>
	 * <li>2: set的字段列表</li>
	 * <li>3: select的字段列表</li>
	 * <li>4: from表</li>
	 * <li>5: from表直到最后的SQL片段</li>
	 * </ul>
	 * 
	 * <pre>
	 * update cd_area set (parentid_, test_, code_) = (select a.asd_, a.s, #daa.AS_ from cd_area where max(a) > 1)
	 * cd_area
	 * parentid_, test_, code_
	 * a.asd_, a.s, #daa.AS_
	 * cd_area
	 * where max(a) > 1)
	 * </pre>
	 * 
	 * @return 更新语句的正则表达式
	 */
	private static Pattern getUpdateSQLPattern() {
		String tablePattern = "[#_a-zA-Z0-9]+";
		String fieldListPattern = "[#\\._a-zA-Z0-9, ]+";
		StringBuilder patternBuilder = new StringBuilder();
		patternBuilder.append("update\\s("); // update空格
		patternBuilder.append(tablePattern); // 表
		patternBuilder.append(")\\sset\\s"); // 空格set空格
		patternBuilder.append("\\(("); // (
		patternBuilder.append(fieldListPattern); // 逗号分隔的字段列表。
		patternBuilder.append(")\\)"); // )
		patternBuilder.append("[\\s]?=[\\s]?"); // 可选的空格 = 可选的空格
		patternBuilder.append("\\("); // (
		patternBuilder.append("select\\s("); // select空格
		patternBuilder.append(fieldListPattern); // 逗号分隔的字段列表。
		patternBuilder.append(")\\sfrom\\s("); // 空格from空格
		patternBuilder.append(tablePattern); // 表
		patternBuilder.append(")");
		patternBuilder.append("(.+)\\)$"); // 任意字符)结尾
		return Pattern.compile(patternBuilder.toString());
	}

	public static Pattern getAliasPattern() {
		String tablePattern = "[#_a-zA-Z0-9]+";
		StringBuilder patternBuilder = new StringBuilder();
		patternBuilder.append("\\s("); // 空格
		patternBuilder.append(tablePattern); // 别名
		patternBuilder.append(")\\swhere\\s"); // 空格where空格
		patternBuilder.append("(.+)"); // 任意字符)结尾
		return Pattern.compile(patternBuilder.toString());
	}

	public static String queryText(Connection conn, String sql) {
		StringBuilder stringBuilder = new StringBuilder();
		try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {
			while (rs.next()) {
				stringBuilder.append(rs.getString(1));
			}

			if (stringBuilder.length() == 0) {
				return null;
			} else {
				return stringBuilder.toString();
			}
		} catch (SQLException e) {
			throw new DBException(sql, e);
		}
	}

	public static Object queryScalar(Connection conn, String sql, int type) {
		try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {
			while (rs.next()) {
				switch (type) {
				case Types.INTEGER:
					return rs.getInt(1);
				case Types.VARCHAR:
					return rs.getString(1);
				default:
					throw new UnsupportedException();
				}
			}
			return null;
		} catch (SQLException e) {
			throw new DBException(sql, e);
		}
	}

	public static int executeUpdate(Connection conn, String sql) {
		try (Statement stmt = conn.createStatement();) {
			return stmt.executeUpdate(sql);
		} catch (SQLException e) {
			throw new DBException(sql, e);
		}
	}

	public static String translateJoinUpdate(String sql, DatabaseType databaseType) {
		switch (databaseType) {
		case ORACLE:
			return sql;
		case SQLSERVER:
			return translateJoinUpdateForSQLServer(sql);
		default:
			throw new UnsupportedException();
		}
	}

	private static String translateJoinUpdateForSQLServer(String sql) {
		Matcher matcher = JOIN_UPDATE_PATTERN.matcher(sql);
		List<String> sqlParts = new ArrayList<>();
		while (matcher.find()) {
			for (int i = 0; i <= matcher.groupCount(); i++) {
				sqlParts.add(matcher.group(i));
			}
		}

		if (sqlParts.size() < 6) {
			throw new DBException("将SQL翻译到SQLServer语法时发生错误，请检查您的SQL：" + sql);
		}

		String updateTable = sqlParts.get(1);
		String[] targetColumns = sqlParts.get(2).split(",");
		String sourceColumnSql = sqlParts.get(3);
		String selectTable = sqlParts.get(4);
		String lastPart = transformLastPart(updateTable, selectTable, sqlParts.get(5));
		String[] sourceColumns = sourceColumnSql.split(",");
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("update ");
		sqlBuilder.append(updateTable);
		sqlBuilder.append(" set ");
		for (int i = 0; i < targetColumns.length; i++) {
			sqlBuilder.append(targetColumns[i]);
			sqlBuilder.append(" = ");
			sqlBuilder.append(sourceColumns[i]);
			sqlBuilder.append(", ");
		}
		sqlBuilder.deleteCharAt(sqlBuilder.length() - 2);// -2因为包含一个空格，包含空格是为了超长时在表格中方便换行。
		sqlBuilder.append(" from ");
		sqlBuilder.append(selectTable);
		sqlBuilder.append(lastPart);
		return sqlBuilder.toString();
	}

	/**
	 * 对于批量更新，并且是更新自身的情况，需要特殊处理。
	 * 
	 * @param updateTable
	 *            更新表。
	 * @param selectTable
	 *            查询表。
	 * @param lastPart
	 *            初步解析后select表之后的部分。(update A set (a) = (select a from A where
	 *            xxxx ) )，其中where xxxx即是lastPart。
	 * @return 最终的lastPart。
	 */
	private static String transformLastPart(String updateTable, String selectTable, String lastPart) {
		// 对于update表和select表相同的情况，需要特别处理。
		if (updateTable.equals(selectTable)) {
			Matcher matcher2 = ALIAS_PATTERN.matcher(lastPart);
			if (matcher2.find()) {
				String alias = matcher2.group(1); // 取出别名的部分
				String where = matcher2.group(2); // 取出where的部分
				StringBuilder lastBuilder = new StringBuilder();
				lastBuilder.append(" left join ");
				lastBuilder.append(selectTable);
				lastBuilder.append(" ");
				lastBuilder.append(alias);
				lastBuilder.append(" on ");
				lastBuilder.append(where);
				return lastBuilder.toString();
			}
		}
		return lastPart;
	}

}
